﻿
CREATE TABLE [dbo].[Holiday] (
    [Id]              INT           IDENTITY (1, 1) NOT NULL,
    [StateProvinceId] INT           NULL,
    [Name]            VARCHAR (255) NULL,
    [CountryCode]     VARCHAR (5)   NULL,
    [Description]     VARCHAR (400) NULL,
    [DisplayOrder]    INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    FOREIGN KEY ([StateProvinceId]) REFERENCES [dbo].[StateProvince] ([Id])
);

GO

CREATE TABLE [dbo].[HolidayDate] (
    [Id]        INT      IDENTITY (1, 1) NOT NULL,
    [HolidayId] INT      NOT NULL,
    [Date]      DATETIME NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [HolidayDate_Holiday] FOREIGN KEY ([HolidayId]) REFERENCES [dbo].[Holiday] ([Id]) ON DELETE CASCADE
);


GO

CREATE NONCLUSTERED INDEX [IX_Holiday_DisplayOrder]
    ON [dbo].[Holiday]([DisplayOrder] ASC);


CREATE NONCLUSTERED INDEX [IX_Holiday_StateProvinceId]
    ON [dbo].[Holiday]([StateProvinceId] ASC);

CREATE NONCLUSTERED INDEX [IX_HolidayDate_HolidayId]
    ON [dbo].[HolidayDate]([HolidayId] ASC);


CREATE NONCLUSTERED INDEX [IX_HolidayDate_Date]
    ON [dbo].[HolidayDate]([Date] ASC);




GO